package com.booza.dao;

import java.math.BigInteger;
import java.util.Calendar;
import java.util.List;

import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.booza.model.Stats;
import com.booza.util.ConverterUtil;

/**
 * A hibernate DAO class for fetching statistics. No business logic here. Just the interaction with database.
 * 
 * @see StatisticsDAO
 */
@Service("statisticsDAO")
@Transactional
@SuppressWarnings( { "nls", "rawtypes" })
public class StatisticsDAOImpl implements StatisticsDAO
{
	@Autowired
	private SessionFactory sessionFactory;

	private static final String PLACEHOLDER = ":lst";

	private static final String PLAYER_PLACEHOLDER = ":pl";

	private static final String TIME_PLACEHOLDER = ":time";

	private static final String OPERATOR_PLACEHOLDER = ":op";

	/**
	 * Queries to calculate rankings table.
	 * 
	 * All queries have a common inner query, which is a union of two queries:
	 * 
	 * <code>SELECT columns FROM stats table WHERE player1 = thisuser UNION SELECT columns FROM stats table WHERE player2 = thisuser</code> .
	 * 
	 * Since the user can be either player1 or player2, we need to have a union of these two select queries. The queries differ only in the where clause and
	 * sometimes in the columns selected. For Ex: To calculate most wins, we do a union of all entries in stats table where score1 > score2 and all entries in
	 * stats where score2 > score1 (the logic is the same). If user is player1 then score1 should be greater than score2 and if he is player2 score 2 > score1.
	 * 
	 */
	private static final String MOSTWINS = "SELECT player, userid, COUNT(*) AS wins, name FROM country,("
			+ "(SELECT a.country, a.userid, stats.player1 AS player, stats.score1, stats.wager FROM account a INNER JOIN stats ON accountid = stats.player1 "
			+ "WHERE score1 > score2  AND gametypeid IN :lst AND wager = 0 AND (leagueid IS NULL OR leagueid = 0) AND (tournamentid IS NULL OR tournamentid = 0)) UNION ALL "
			+ "(SELECT a.country, a.userid, stats.player2 AS player, stats.score2, stats.wager FROM account a INNER JOIN stats ON accountid = stats.player2 "
			+ "WHERE score2 > score1 AND gametypeid IN :lst AND wager = 0 AND (leagueid IS NULL OR leagueid = 0) AND (tournamentid IS NULL OR tournamentid = 0))) AS t1 WHERE country.countryid = country GROUP BY userid ORDER BY wins DESC";

	/**
	 * The difference here is that we do order by wager amount to order the results based on total amount won.
	 */
	private static final String WAGERWINS = "SELECT player, userid, COUNT(*), sum(wager) AS wagersum, name "
			+ "FROM country,((SELECT a.country, a.userid, stats.player1 AS player, stats.score1, stats.wager FROM account a INNER JOIN stats ON accountid"
			+ "= stats.player1 WHERE score1 > score2 AND gametypeid IN :lst AND wager > 0 AND (leagueid IS NULL OR leagueid = 0) AND (tournamentid IS NULL OR tournamentid = 0)) UNION ALL "
			+ "(SELECT a.country, a.userid, stats.player2 AS player, stats.score2, stats.wager FROM account a INNER JOIN stats ON accountid"
			+ "= stats.player2 WHERE score2 > score1 AND gametypeid IN :lst AND wager > 0 AND (leagueid IS NULL OR leagueid = 0) AND (tournamentid IS NULL OR tournamentid = 0))) AS t1 WHERE country.countryid = country GROUP BY userid ORDER BY wagersum DESC";

	/**
	 * The difference here is the where condition, score should be greater or equal to 100.
	 */
	private static final String CENTURIES = "SELECT player, userid, COUNT(*) AS summation, name FROM country,"
			+ "((SELECT a.country, a.userid, stats.player1 AS player, stats.century1 AS century "
			+ "FROM account a INNER JOIN stats ON accountid = stats.player1 WHERE gametypeid IN :lst AND century1 > 0 AND wager = 0 AND (leagueid IS NULL OR leagueid =0) AND (tournamentid IS NULL OR tournamentid = 0))"
			+ " UNION ALL (SELECT a.country,a.userid,stats.player2 AS player, stats.century2 AS century "
			+ "FROM account a INNER JOIN stats ON accountid = stats.player2 WHERE gametypeid IN :lst AND century2 > 0 AND wager = 0 AND (leagueid IS NULL OR leagueid =0) AND (tournamentid IS NULL OR tournamentid = 0)))"
			+ " AS t1 WHERE country.countryid = country GROUP BY userid ORDER BY summation DESC";

	/**
	 * The difference here is the where condition, score should b greater or equal to 50 and less than 100.
	 */
	private static final String HALFCENTUREIS = "SELECT player, userid, COUNT(*) AS summation, name FROM country,"
			+ "((SELECT a.country, a.userid,stats.player1 AS player FROM account a INNER JOIN stats ON accountid = stats.player1 "
			+ "WHERE gametypeid IN :lst AND stats.score1 >= 50 AND stats.score1 < 100 AND wager = 0 AND (leagueid IS NULL OR leagueid = 0) AND (tournamentid IS NULL OR tournamentid = 0)) "
			+ "UNION ALL (SELECT a.country, a.userid, stats.player2 AS player FROM account a INNER JOIN stats ON accountid = stats.player2 "
			+ "WHERE gametypeid IN :lst AND stats.score2 >= 50 AND stats.score2 <100 AND wager = 0 AND (leagueid IS NULL OR leagueid = 0) AND (tournamentid IS NULL OR tournamentid = 0))) AS t1 WHERE country.countryid = country GROUP BY userid ORDER BY summation DESC";

	// We order the results by sum of goldenbreaks of a user.
	private static final String GOLDENBREAKS = "SELECT player, userid, SUM(goldenbreak) AS summation, name FROM country, "
			+ "((SELECT a.country, a.userid, stats.player1 AS player, stats.goldenbreak1 AS goldenbreak FROM account a INNER JOIN stats ON accountid = stats.player1 "
			+ "WHERE gametypeid IN :lst and goldenbreak1 > 0 AND wager = 0 AND (leagueid IS NULL OR leagueid = 0) AND (tournamentid IS NULL OR tournamentid = 0)) UNION ALL (SELECT a.country,a.userid,stats.player2 AS player, stats.goldenbreak2 AS goldenbreak "
			+ "FROM account a INNER JOIN stats ON accountid = stats.player2 WHERE gametypeid IN :lst AND goldenbreak2 > 0 AND wager = 0 AND (leagueid IS NULL OR leagueid =0) AND (tournamentid IS NULL OR tournamentid = 0))) AS t1 WHERE country.countryid = country "
			+ "GROUP BY userid ORDER BY summation DESC ";

	// We order the results by sum of runouts of a user.
	private static final String RUNOUTS = "SELECT player, userid, SUM(runout) AS summation, name FROM country,"
			+ "((SELECT a.country, a.userid, stats.player1 AS player, stats.runout1 AS runout FROM account a INNER JOIN stats "
			+ "ON accountid = stats.player1 WHERE gametypeid IN :lst AND runout1 > 0 AND wager = 0 AND (leagueid IS NULL OR leagueid =0) AND (tournamentid IS NULL OR tournamentid = 0)) UNION ALL (SELECT a.country, a.userid, stats.player2 AS player,"
			+ "stats.runout2 AS runout FROM account a INNER JOIN stats ON accountid = stats.player2 WHERE gametypeid IN :lst and runout2 > 0 AND wager = 0 AND (leagueid IS NULL OR leagueid =0) AND (tournamentid IS NULL OR tournamentid = 0))) AS t1 WHERE country.countryid = country GROUP BY userid ORDER BY summation DESC ";

	// We order the results by sum of sevenballings of a user.
	private static final String SEVENBALLINGS = "SELECT player, userid, SUM(seven) AS summation, name FROM country,"
			+ "((SELECT a.country, a.userid, stats.player1 AS player, stats.sevenballings1 AS seven FROM account a INNER JOIN "
			+ "stats ON accountid = stats.player1 WHERE gametypeid IN :lst AND sevenballings1 > 0 AND wager = 0 AND (leagueid IS NULL OR leagueid =0) AND (tournamentid IS NULL OR tournamentid = 0)) "
			+ "UNION ALL (SELECT a.country, a.userid, stats.player2 AS player, stats.sevenballings2 AS seven FROM account a "
			+ "INNER JOIN stats ON accountid = stats.player2 WHERE gametypeid IN :lst AND sevenballings2 > 0 AND wager = 0 AND (leagueid IS NULL OR leagueid =0) AND (tournamentid IS NULL OR tournamentid = 0) )) AS t1 WHERE country.countryid=country GROUP BY userid ORDER BY summation DESC ";

	// Rankings.
	// FIXME This is currently inconsistent. Need to look at the game server code.
	private static final String RANKINGS = "SELECT a.accountid, userid, c.name, SUM(points) AS summation "
			+ "FROM playerrankings a, account b, country c WHERE a.accountid = b.accountid AND c.countryid=country AND gameid IN :lst "
			+ "GROUP BY (a.accountid) ORDER BY summation DESC ";

	// Query to select the number of tournaments won by player for a particular game.
	// This is only for regular system generated tournaments.
	private static final String MOST_TOURNAMENT_WINS = "SELECT a.player, b.userid,COUNT(a.player),name FROM account b,country c,"
			+ "(SELECT tournamentid,(CASE WHEN score1 > score2 THEN player1 ELSE player2 END) AS player, ROUND FROM stats "
			+ "WHERE (tournamentid, round) IN (SELECT tournamentid, MAX(round) FROM stats GROUP BY tournamentid) AND gamestatus=2 AND tournamenttype='system' AND gametypeid IN :lst) "
			+ "a WHERE a.player = b.accountid AND b.country = c.countryid GROUP BY b.accountid, a.player";

	// Query to select the number of abandoned games involving this player for a particular game.
	// This is used for the player statistics.
	private static final String TOTAL_GAMES_ABANDONED = "SELECT COUNT(*) FROM stats WHERE gametypeid =:lst AND wager = 0 AND (leagueid IS NULL OR leagueid = 0) AND (tournamentid IS NULL OR tournamentid = 0) AND "
			+ "((player1=:pl AND abandon1=1) OR (player2=:pl AND abandon2=1))";

	// Query to select the number of games won by player for a particular game.
	// This is used for the player statistics.
	private static final String TOTAL_GAMES_WON = "SELECT COUNT(*) AS wins FROM "
			+ "((SELECT a.country, a.userid, stats.player1 AS player, stats.score1, stats.wager FROM account a INNER JOIN stats ON accountid = stats.player1 "
			+ "WHERE gametypeid IN :lst AND wager = 0 AND (leagueid IS NULL OR leagueid = 0) AND (tournamentid IS NULL OR tournamentid = 0) AND stats.player1 = :pl AND ((score1 > score2 AND stats.abandon1 <> 1) OR stats.abandon2 = 1)) UNION ALL "
			+ "(SELECT a.country, a.userid, stats.player2 AS player, stats.score2, stats.wager FROM account a INNER JOIN stats ON accountid = stats.player2 "
			+ "WHERE gametypeid IN :lst AND wager = 0 AND (leagueid IS NULL OR leagueid = 0) AND (tournamentid IS NULL OR tournamentid = 0) AND stats.player2 = :pl AND ((score2 > score1 AND stats.abandon2 <> 1) OR stats.abandon1 = 1))) AS t1 ";

	// Query to select the number of games lost by player for a particular game.
	// This is used for the player statistics.
	private static final String TOTAL_GAMES_LOST = "SELECT COUNT(*) AS wins FROM "
			+ "((SELECT a.country, a.userid, stats.player1 AS player, stats.score1, stats.wager FROM account a INNER JOIN stats ON accountid = stats.player1 "
			+ "WHERE gametypeid IN :lst AND wager = 0 AND (leagueid IS NULL OR leagueid = 0) AND (tournamentid IS NULL OR tournamentid = 0) AND stats.player1 = :pl AND (stats.abandon1 = 1 OR (score1 < score2 AND stats.abandon2 <> 1))) UNION ALL "
			+ "(SELECT a.country, a.userid, stats.score2, stats.player2 AS player, stats.wager FROM account a INNER JOIN stats ON accountid = stats.player2 "
			+ "WHERE gametypeid IN :lst AND wager = 0 AND (leagueid IS NULL OR leagueid = 0) AND (tournamentid IS NULL OR tournamentid = 0) AND stats.player2 = :pl AND (stats.abandon2 = 1 OR (score2 < score1 AND stats.abandon1 <> 1)))) AS t1 ";

	// This is used for the financials.
	private static final String PLAYER_STATS = "SELECT wager, player, tournamentid, finishtime, gamename FROM "
			+ "((SELECT stats.player1 AS player, stats.wager AS wager, stats.tournamentid AS tournamentid, stats.finishtime AS finishtime,"
			+ "stats.gamename AS gamename FROM account a INNER JOIN stats ON accountid=stats.player1 "
			+ "WHERE score1 :op score2 AND gametypeid IN :lst AND stats.player1=:pl :time) UNION ALL "
			+ "(SELECT stats.player2 AS player,stats.wager AS wager,stats.tournamentid AS tournamentid, stats.finishtime AS finishtime,"
			+ "stats.gamename AS gamename FROM account a INNER JOIN stats ON accountid = stats.player2 WHERE score2 :op score1 "
			+ "AND gametypeid IN :lst AND stats.player2=:pl :time)) AS t1 ";

	// This is used for the financials.
	private static final String PLAYER_STATS_ABANDONEDGAMES = "SELECT wager, player, tournamentid, finishtime, gamename "
			+ "FROM ((SELECT stats.player1 AS player,stats.wager AS wager, stats.tournamentid AS tournamentid,"
			+ "stats.finishtime AS finishtime,stats.gamename AS gamename FROM account a INNER JOIN stats ON "
			+ "accountid=stats.player1 WHERE score1 :op score2 AND gamestatus = 1 AND gametypeid in :lst AND stats.player1=:pl :time) "
			+ "UNION ALL (SELECT stats.player2 AS player, stats.wager AS wager,stats.tournamentid AS tournamentid, stats.finishtime AS finishtime,"
			+ "stats.gamename AS gamename FROM account a INNER JOIN stats ON accountid = stats.player2 WHERE score2 :op score1 "
			+ "AND gamestatus = 1 AND gametypeid IN :lst AND stats.player2=:pl :time)) AS t1 ";

	/**
	 * Find players with most wins.
	 * 
	 * @param gameType
	 *          - game for which this statistics applies to. Can be any one game or all games
	 * @return Return players with most wins in descending order.
	 */
	@Override
	public List getPlayersWithMostWins(List<String> gameType)
	{
		return this.sessionFactory.getCurrentSession().createSQLQuery(
				MOSTWINS.replace(PLACEHOLDER, ConverterUtil.listToSQLString(gameType))).list();
	}

	/**
	 * Find players with most wager wins.
	 * 
	 * @param gameType
	 *          - game for which this statistics applies to. Can be any one game or all games
	 * @return Return players with most wager wins in descending order.
	 */
	@Override
	public List getPlayersWithMostWagerWins(List<String> gameType)
	{
		return this.sessionFactory.getCurrentSession().createSQLQuery(
				WAGERWINS.replace(PLACEHOLDER, ConverterUtil.listToSQLString(gameType))).list();
	}

	/**
	 * Find players with most centuries.
	 * 
	 * @param gameType
	 *          - game for which this statistics applies to. Can be any one game or all games
	 * @return Return players with most centuries in descending order.
	 */
	@Override
	public List getPlayersWithMostCenturies(List<String> gameType)
	{
		return this.sessionFactory.getCurrentSession().createSQLQuery(
				CENTURIES.replace(PLACEHOLDER, ConverterUtil.listToSQLString(gameType))).list();
	}

	/**
	 * Find players with most half centuries.
	 * 
	 * @param gameType
	 *          - game for which this statistics applies to. Can be any one game or all games
	 * @return Return players with most half centuries in descending order.
	 */
	@Override
	public List getPlayersWithMostHalfCenturies(List<String> gameType)
	{
		return this.sessionFactory.getCurrentSession().createSQLQuery(
				HALFCENTUREIS.replace(PLACEHOLDER, ConverterUtil.listToSQLString(gameType))).list();
	}

	/**
	 * Find players with most golden breaks.
	 * 
	 * @param gameType
	 *          - game for which this statistics applies to. Can be any one game or all games
	 * @return Return players with most golden breaks in descending order
	 */
	@Override
	public List getPlayersWithMostGoldenBreaks(List<String> gameType)
	{
		return this.sessionFactory.getCurrentSession().createSQLQuery(
				GOLDENBREAKS.replace(PLACEHOLDER, ConverterUtil.listToSQLString(gameType))).list();
	}

	/**
	 * Find players with most runouts.
	 * 
	 * @param gameType
	 *          - game for which this statistics applies to. Can be any one game or all games
	 * @return Return players with most run outs in descending order
	 */
	@Override
	public List getPlayersWithMostRunouts(List<String> gameType)
	{
		return this.sessionFactory.getCurrentSession().createSQLQuery(
				RUNOUTS.replace(PLACEHOLDER, ConverterUtil.listToSQLString(gameType))).list();
	}

	/**
	 * Find players with most seven ballings.
	 * 
	 * @param gameType
	 *          - game for which this statistics applies to. Can be any one game or all games
	 * @return Return players with most sevenballings in descending order
	 */
	@Override
	public List getPlayersWithMostSevenBallings(List<String> gameType)
	{
		return this.sessionFactory.getCurrentSession().createSQLQuery(
				SEVENBALLINGS.replace(PLACEHOLDER, ConverterUtil.listToSQLString(gameType))).list();
	}

	/**
	 * Find a row in statistics table.
	 * 
	 * @param id
	 *          - id of the row
	 * @return statistics
	 */
	@Override
	public Stats findStats(Integer id)
	{
		return (Stats) this.sessionFactory.getCurrentSession().get(Stats.class, id);
	}

	/**
	 * Find players with most rankings.
	 * 
	 * @return Return players with most rankings in descending order
	 */
	@Override
	public List findOverallRankings(List<String> gameType)
	{
		return this.sessionFactory.getCurrentSession().createSQLQuery(
				RANKINGS.replace(PLACEHOLDER, ConverterUtil.listToSQLString(gameType))).list();
	}

	/**
	 * Find number of games lost by player for a particular game.
	 * 
	 * @param accountId
	 * @param gameType
	 * @return
	 */
	@Override
	public List findLossesByPlayer(Integer accountId, List<String> gameType)
	{
		return this.sessionFactory.getCurrentSession().createSQLQuery(
				TOTAL_GAMES_LOST.replace(PLACEHOLDER, ConverterUtil.listToSQLString(gameType)).replace(PLAYER_PLACEHOLDER,
						accountId + "")).list();
	}

	/**
	 * Find number of games won by player for a particular game.
	 * 
	 * @param accountId
	 * @param gameType
	 * @return
	 */
	@Override
	public List findWinsByPlayer(Integer accountId, List<String> gameType)
	{
		return this.sessionFactory.getCurrentSession().createSQLQuery(
				TOTAL_GAMES_WON.replace(PLACEHOLDER, ConverterUtil.listToSQLString(gameType)).replace(PLAYER_PLACEHOLDER,
						accountId + "")).list();
	}

	/**
	 * Find number of abandoned games involved by player for a particular game.
	 * 
	 * @param accountId
	 * @param gameType
	 * @return
	 */
	@Override
	public List findAbandonedGamesForPlayer(Integer accountId, List<String> gameType)
	{
		return this.sessionFactory.getCurrentSession().createSQLQuery(
				TOTAL_GAMES_ABANDONED.replace(PLACEHOLDER, ConverterUtil.listToSQLString(gameType)).replace(
						PLAYER_PLACEHOLDER, accountId + "")).list();
	}

	/**
	 * Find player statistics from stats table.
	 */
	@Override
	public List findPlayerStats(Integer accountId, List<String> gameType, String winCondition, String timeFrame)
	{
		return this.sessionFactory.getCurrentSession().createSQLQuery(
				PLAYER_STATS.replace(PLACEHOLDER, ConverterUtil.listToSQLString(gameType)).replace(TIME_PLACEHOLDER,
						resolveTimeFrame(timeFrame)).replace(OPERATOR_PLACEHOLDER, winCondition).replace(PLAYER_PLACEHOLDER,
						accountId + "")).list();
	}

	/**
	 * Find player statistics from stats table. Statistics for games which are abandoned by this player.
	 * 
	 * @param accountId
	 * @param gameType
	 * @param winCondition
	 * @param timeFrame
	 * @return
	 */
	@Override
	public List findPlayerStatsForAbandonedGames(Integer accountId, List<String> gameType, String winCondition,
			String timeFrame)
	{
		return this.sessionFactory.getCurrentSession().createSQLQuery(
				PLAYER_STATS_ABANDONEDGAMES.replace(PLACEHOLDER, ConverterUtil.listToSQLString(gameType)).replace(
						TIME_PLACEHOLDER, resolveTimeFrame(timeFrame)).replace(OPERATOR_PLACEHOLDER, winCondition).replace(
						PLAYER_PLACEHOLDER, accountId + "")).list();
	}

	/**
	 * Finds the total number of games played by given player.
	 * 
	 * @param accountId
	 * @return
	 */
	@Override
	public Integer findTotalGamesPlayed(Integer accountId)
	{
		BigInteger result = (BigInteger) this.sessionFactory.getCurrentSession().createSQLQuery(
				"SELECT COUNT(*) FROM stats WHERE " + "player1 = " + accountId + " OR player2=" + accountId).uniqueResult();
		return result.intValue();
	}

	/**
	 * Finds Players with the most tournament wins.
	 * 
	 * @param gameType
	 * @return
	 */
	@Override
	public List getMostTournamentWins(List<String> gameType)
	{
		return this.sessionFactory.getCurrentSession().createSQLQuery(
				MOST_TOURNAMENT_WINS.replace(PLACEHOLDER, ConverterUtil.listToSQLString(gameType))).list();
	}

	private String resolveTimeFrame(String timeframe)
	{
		if ("7".equalsIgnoreCase(timeframe)) {
			return "AND datediff(current_date,finishtime) <= 7";
		}
		else if ("W".equalsIgnoreCase(timeframe)) {
			Calendar cal = Calendar.getInstance();
			int days = cal.get(Calendar.DAY_OF_WEEK);
			return "AND datediff(current_date,finishtime) <=" + days;
		}
		else if ("30".equalsIgnoreCase(timeframe)) {
			return "AND datediff(current_date,finishtime) <= 30";
		}
		else if ("M".equalsIgnoreCase(timeframe)) {
			Calendar cal = Calendar.getInstance();
			int days = cal.get(Calendar.DAY_OF_MONTH);
			return "AND datediff(current_date,finishtime) <=" + days;
		}
		else if ("12".equalsIgnoreCase(timeframe)) {
			return "AND datediff(current_date,finishtime) <= 365";
		}
		else if ("Y".equalsIgnoreCase(timeframe)) {
			return "AND datediff(current_date,finishtime) <= 365"; // TODO: Needs to be fixed
		}
		else return "";
	}
	
	@SuppressWarnings("unchecked")
	public List<Stats> getStatsForLeague(Integer leagueId) {
		return sessionFactory.getCurrentSession().createCriteria(Stats.class)
				.add(Restrictions.eq("leagueid", leagueId)).list();
	}
}
